package com.ignou.mca.datasource;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;
import java.sql.Date;
import java.text.SimpleDateFormat;
import java.util.StringTokenizer;

import javax.mail.MessagingException;
import javax.mail.internet.AddressException;

import com.ignou.mca.beans.ExamBean;
import com.ignou.mca.beans.ExamStatusBean;
import com.ignou.mca.beans.MailBean;
import com.ignou.mca.beans.PaymentBean;
import com.ignou.mca.beans.QuestionsBean;
import com.ignou.mca.beans.RegistrationVO;
import com.ignou.mca.beans.SubjectBean;
import com.ignou.mca.beans.UserVO;
import com.ignou.mca.utils.MailUtilities;
import com.ignou.mca.utils.RandamNumberGeneration;

public class DatabaseOperations
{
	Connection conn = null;
	
	public DatabaseOperations() 
    {
		if(conn==null)
		{
			conn = DatabaseConnectionManager.getConnection();
		}
		else
		{
			System.out.println("Connection Already exists.");
		}
    }
	
	//---------------------------------begin of registration operations-----------------------------------
	public UserVO doLogin(String userName,String password)
	{
		UserVO uVO = null;
		
		if(conn==null)
		{
			conn = DatabaseConnectionManager.getConnection();
		}
			
		try
		{
				Statement stmt = conn.createStatement();
				ResultSet rs = stmt.executeQuery("select * from users where userid='"+userName+"' AND activestatus='0'");
				uVO = new UserVO();
				if(rs.next())
				{
					uVO.setUserName(rs.getString(1));
					uVO.setPassword(rs.getString(2));
					uVO.setFirstName(rs.getString(3));
					uVO.setLastName(rs.getString(4));
					uVO.setEmail(rs.getString(5));
					uVO.setPhone(rs.getString(6));
					uVO.setAddress1(rs.getString(7));
					uVO.setAddress2(rs.getString(8));
					uVO.setCity(rs.getString(9));
					uVO.setState(rs.getString(10));
					uVO.setCountry(rs.getString(11));
					uVO.setRole(rs.getString(12));
					uVO.setActiveStatus(rs.getString(13));
					uVO.setLastLogin(rs.getString(14));
				}else
				{
					uVO = null;
				}
			}catch(SQLException e)
			{
				e.printStackTrace();
			}
			
		return uVO;
	}
	
	public Boolean updateLastLogin(String userId)
	{
		Boolean isUpdated = false;
		if(conn==null)
		{
			conn = DatabaseConnectionManager.getConnection();
		}
		
		java.util.Date dt = new java.util.Date();
		try
		{
			PreparedStatement pstmt = conn.prepareStatement("update users set lastLogin=? where userID=?");
			pstmt.setString(1, dt.toString());
			pstmt.setString(2, userId);
			int i = pstmt.executeUpdate();
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}
		return true;
	}
	
	public Boolean changePassword(String userName, String oldPassword, String newPassword)
	{
		Boolean isChanged = false;
		if(conn==null)
		{
			conn = DatabaseConnectionManager.getConnection();
		}
		
		try
		{
			PreparedStatement pstmt = conn.prepareStatement("update users set password=? where userID=?");
			pstmt.setString(1, newPassword);
			pstmt.setString(2, userName);
			int i = pstmt.executeUpdate();
			
			if(i==0)
			{
				isChanged = true;
			}
			else
			{
				isChanged = false;
			}
		}
		catch(SQLException e)
		{
			isChanged = false;
			e.printStackTrace();
		}
		return isChanged;
	}
	
	
	public Boolean doRegistration(RegistrationVO regVO, String activationLink)
	{
		Boolean doneRegistration = false;
		if(conn==null)
		{
			conn = DatabaseConnectionManager.getConnection();
		}
		
		try
		{
			String random = RandamNumberGeneration.rand(350,500)+RandamNumberGeneration.randomstring(3,10)+RandamNumberGeneration.rand(550,700);
			Calendar cal = Calendar.getInstance();
			Date dd = new Date(cal.getTimeInMillis());
			
			String query = "insert into users(userID,password,FirstName,LastName,Address1," +
			"Adress2,city,state,country,phone,email,securityQuestion,securityAnswer," +
			"role,activeStatus,RandomNumber,activationDate,LastLogin) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
			
			PreparedStatement pstmt = conn.prepareStatement(query);
			pstmt.setString(1, regVO.getUserName());
			pstmt.setString(2, regVO.getPassword());
			pstmt.setString(3, regVO.getFirstName());
			pstmt.setString(4, regVO.getLastName());
			pstmt.setString(5, regVO.getAddress1());
			pstmt.setString(6, regVO.getAddress2());
			pstmt.setString(7, regVO.getCity());
			pstmt.setString(8, regVO.getState());
			pstmt.setString(9, regVO.getCountry());
			pstmt.setString(10, regVO.getPhone());
			pstmt.setString(11, regVO.getEmail());
			pstmt.setString(12, regVO.getSecurityQuestion());
			pstmt.setString(13, regVO.getSecurityAnswer());
			pstmt.setString(14, regVO.getRole());
			pstmt.setString(15, regVO.getActiveStatus());
			pstmt.setString(16, random);
			pstmt.setDate(17, dd);
			pstmt.setString(18, dd.toString());

			int i = pstmt.executeUpdate();
				
			if(i==1)
			{
				MailBean mb = new MailBean();
				mb.setUserName(regVO.getUserName());
				mb.setEmail(regVO.getEmail());
				mb.setPassword(regVO.getPassword());
				mb.setFirstName(regVO.getFirstName());
				mb.setLastName(regVO.getLastName());
				mb.setActivationLink(activationLink);
				mb.setRandomNumber(random);
				doneRegistration = true;
				try 
				{
					MailUtilities.sendMail("register", mb);
				} catch (AddressException e) 
				{
					// TODO Auto-generated catch block
					e.printStackTrace();
				} 
				catch (MessagingException e) 
				{
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}else
			{
				doneRegistration = false;
			}

		}
		catch(SQLException e)
		{
			doneRegistration = false;
			e.printStackTrace();
		}
		return doneRegistration;
	}
	
	public Boolean checkUserIdExists(String userId)
	{
		Boolean isExists = true;
		if(conn==null)
		{
			conn = DatabaseConnectionManager.getConnection();
		}
		
		try
		{
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("select userId from users where userId='"+userId+"'");
			if(rs.next())
			{
				isExists = true;
			}
			else
			{
				isExists = false;
			}
		}catch(Exception e)
		{
			isExists = true;
			e.printStackTrace();
		}
		return isExists;
	}
	
	public Boolean checkEmailExists(String email)
	{
		Boolean isExists = true;
		if(conn==null)
		{
			conn = DatabaseConnectionManager.getConnection();
		}
		
		try
		{
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("select email from users where email='"+email+"'");
			if(rs.next())
			{
				isExists = true;
			}
			else
			{
				isExists = false;
			}
		}catch(Exception e)
		{
			isExists = true;
			e.printStackTrace();
		}
		return isExists;
	}
	
	public String doActivation(String userName,String email, String activeCode)
	{
		String isActivated = null;
		if(conn==null)
		{
			conn = DatabaseConnectionManager.getConnection();
		}
		
		try
		{
			String random = "";
			Statement stmt = conn.createStatement();
			PreparedStatement pstmt;
			String query = "select activationdate,activestatus,email,userid from users where randomnumber='"
				+ activeCode.trim()+ "' AND email='"+email+"'";

			ResultSet rs = stmt.executeQuery(query);
			Calendar now1 = Calendar.getInstance();
			Calendar now = Calendar.getInstance();
			SimpleDateFormat dayformat = new SimpleDateFormat("dd/mm/yyyy");
			java.util.Date d = new java.util.Date();

			String date = null;
			java.util.Date d1 = null;
			
			if (rs.next()) {
				java.util.Date d2 = (java.util.Date)rs.getDate(1);
				if(d2==null) d2 = new java.util.Date();
				date = dayformat.format(d2);
				email = rs.getString(3);
				d1 = new java.util.Date(date + " " + rs.getTime(1));
				now1.setTime(d1);
				pstmt = conn.prepareStatement("update users set activeStatus=?,activationdate=? where randomnumber=?");
				pstmt.setString(1,"0");
				pstmt.setString(2, getCurrentDate());
				pstmt.setString(3, activeCode.trim());

				long h = (now.getTime().getTime()) - (now1.getTime().getTime());
				long t = h / (60 * 1000);
				if (t <= 1440) {
					System.out.println(rs.getString(2) + "  ");
					if (rs.getString(2).equals("0")) 
					{
						//User Already activated.
						isActivated = "User Already activated";
					} else 
					{
						int i = pstmt.executeUpdate();
						if (i == 1) 
						{
							//activated
							isActivated = "activated";
						}
					}
				} else {

					if (rs.getBoolean(2)) {
						isActivated = "This user is already active";
					} else {
						pstmt = conn.prepareStatement("delete from users where randomnumber=?");
						pstmt.setString(1, activeCode.trim());
						int i = pstmt.executeUpdate();
						//Activation has expired and user details deleted.
						isActivated = "Activation has expired and user details deleted.";
					}
				}
			}else
			{
				//The activation you entered is wrong. Please check and try again."
				System.out.println("The activation you entered is wrong. Please check and try again.");
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		
		return isActivated;
	}
	
	private static String getCurrentDate() 
	{
		// TODO Auto-generated method stub
		java.util.Date currentDate = new java.util.Date();

		java.sql.Date sqlDate = new java.sql.Date(currentDate.getTime());
		// System.out.println("date"+sqlDate);

		return sqlDate + " " + currentDate.getHours() + ":"
				+ currentDate.getMinutes() + ":" + currentDate.getSeconds()
				+ ".0";
	}
	
	
	public String forgotPassword(String userName, String secureAns,String email)
	{
		String isRetrived = "";
		if(conn==null)
		{
			conn = DatabaseConnectionManager.getConnection();
		}
		
		try
		{
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("select securityAnswer,password,Email from users where userid='"+userName+"' AND Email='"+email+"'");
			String dbSecAns = "";
			String dbPwd = "";
			String dbEmail = "";
			if(rs.next())
			{
				dbSecAns = rs.getString(1);
				dbPwd = rs.getString(2);
				dbEmail = rs.getString(3);
			}
			
			if(dbSecAns.equals(secureAns))
			{
				isRetrived = dbPwd;
				MailBean mb = new MailBean();
				mb.setPassword(dbPwd);
				mb.setUserName(userName);
				mb.setEmail(dbEmail);
				MailUtilities.sendMail("forgotPwd", mb);
			}else
			{
				isRetrived = null;
			}
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		} catch (AddressException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (MessagingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return isRetrived;
	}
	
	public String getSecutiryQuestion(String userName)
	{
		String ques = null;
		
		if(conn==null)
		{
			conn = DatabaseConnectionManager.getConnection();
		}
		
		try
		{
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("select securityQuestion from users where userid='"+userName+"'");
			if(rs.next())
			{
				ques = rs.getString(1);
			}
			
		}catch(SQLException e)
		{
			e.printStackTrace();
		}
		
		return ques;
	}
//---------------------------------end of registration operations-----------------------------------	
//---------------------------------subject related operations-----------------------------------	
	public Boolean addSubjectDetails(SubjectBean sb)
	{
		Boolean isAdded = false;
		if(conn==null)
		{
			conn = DatabaseConnectionManager.getConnection();
		}
		try
		{
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("select subjectName from subjects where subjectName='"+sb.getSubjectName()+"'");
			if(rs.next())
			{
				isAdded = false;
			}else
			{
				PreparedStatement pstmt = conn.prepareStatement("insert into subjects (subjectName) values(?)");
				pstmt.setString(1, sb.getSubjectName().toUpperCase());
				
				int i = pstmt.executeUpdate();
				if(i==1)
				{
					isAdded = true;
				}
			}
			
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}

		return isAdded;
	}
	
	public Boolean deleteSubject(int subjectId)
	{
		Boolean isDeleted = false;
		if(conn==null)
		{
			conn = DatabaseConnectionManager.getConnection();
		}
		try
		{
			PreparedStatement pstmt = conn.prepareStatement("delete from subjects where subjectId=?");
			pstmt.setInt(1, subjectId);
			
			int i = pstmt.executeUpdate();
			if(i==1)
			{
				isDeleted = true;
			}
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}
		
		return isDeleted;
	}
	
	public ArrayList<SubjectBean> getUserSubjects(String userName)
	{
		ArrayList<SubjectBean> userSubjects = new ArrayList<SubjectBean>();
		
		if(conn==null)
		{
			conn = DatabaseConnectionManager.getConnection();
		}
		
		try
		{
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("select subjectIDs from userStatus where userID='"+userName+"'");
			StringTokenizer st = null;
			if(rs.next())
			{
				String subjects = rs.getString(1);
				st = new StringTokenizer(subjects,"|");
				while(st.hasMoreTokens())
				{
					stmt = conn.createStatement();
					rs = stmt.executeQuery("select * from subjects where subjectID='"+st.nextToken()+"'");
					while(rs.next())
					{
						SubjectBean sb = new SubjectBean();
						sb.setSubjectId(rs.getInt(1));
						sb.setSubjectName(rs.getString(2));
						userSubjects.add(sb);
					}
				}
				
			}
		}catch(Exception e)
		{
			e.printStackTrace();
		}
		return userSubjects;
	}
	
	public ArrayList<SubjectBean> getAllSubjects()
	{
		ArrayList<SubjectBean> allSubjects = new ArrayList<SubjectBean>();
		
		if(conn==null)
		{
			conn = DatabaseConnectionManager.getConnection();
		}
		
		try
		{
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("select * from subjects");
			while(rs.next())
			{
				SubjectBean sb = new SubjectBean();
				int subjectID = rs.getInt(1);
				String subjectName = rs.getString(2);
				
				sb.setSubjectId(subjectID);
				sb.setSubjectName(subjectName);
				allSubjects.add(sb);
			}
		}catch(Exception e)
		{
			e.printStackTrace();
		}
		
		return allSubjects;
	}
	
	public SubjectBean getSubjectDetails(int subID)
	{
		SubjectBean subjectDetails = new SubjectBean();
		if(conn==null)
		{
			conn = DatabaseConnectionManager.getConnection();
		}
		
		try
		{
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("select * from subjects where subjectID='"+subID+"'");
			if(rs.next())
			{
				subjectDetails.setSubjectId(rs.getInt(1));
				subjectDetails.setSubjectName(rs.getString(2));
			}else
			{
				subjectDetails = null;
			}
		}catch(Exception e)
		{
			e.printStackTrace();
		}
		
		return subjectDetails;
	}

	public Boolean updateSubjectDetails(SubjectBean sb)
	{
		Boolean isUpdated = false;
		if(conn==null)
		{
			conn = DatabaseConnectionManager.getConnection();
		}
		try
		{
			PreparedStatement pstmt = conn.prepareStatement("update subjects set subjectName=? where subjectID=?");
			pstmt.setString(1, sb.getSubjectName().toUpperCase());
			pstmt.setInt(2, sb.getSubjectId());
			int i = pstmt.executeUpdate();
			if(i==1)
			{
				isUpdated = true;
			}
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}

		return isUpdated;
	}
//--------------------------------- end of subject related operations-----------------------------------
//--------------------------------- exam related operations --------------------------------------------
	
	public ArrayList<ExamBean> getSubjectRelatedExams(int subjectId)
	{
		ArrayList<ExamBean> userSubjects = new ArrayList<ExamBean>();
		
		if(conn==null)
		{
			conn = DatabaseConnectionManager.getConnection();
		}
		
		try
		{
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("select * from exam where subjectID='"+subjectId+"'");
			StringTokenizer st = null;
			while(rs.next())
			{
				ExamBean eb = new ExamBean();
				eb.setExamId(rs.getInt(1));
				eb.setExamName(rs.getString(2));
				eb.setSubjectId(rs.getInt(3));
				eb.setNoOfQuestions(rs.getInt(4));
				eb.setQuestionIds(rs.getString(5));
				eb.setMaxMarks(rs.getInt(6));
				eb.setPassMarks(rs.getInt(7));
				eb.setExamFees(rs.getInt(8));
				eb.setDuration(rs.getInt(9));
				userSubjects.add(eb);
			}
		}catch(Exception e)
		{
			e.printStackTrace();
		}
		return userSubjects;
	}
	public ExamBean getExamDetails(int examId)
	{
		ExamBean eb = new ExamBean();
		if(conn==null)
		{
			conn = DatabaseConnectionManager.getConnection();
		}
		
		try
		{
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("select * from exam where examId="+examId+"");
			if(rs.next())
			{
				eb.setExamId(rs.getInt(1));
				eb.setExamName(rs.getString(2));
				eb.setSubjectId(rs.getInt(3));
				eb.setNoOfQuestions(rs.getInt(4));
				eb.setQuestionIds(rs.getString(5));
				eb.setMaxMarks(rs.getInt(6));
				eb.setPassMarks(rs.getInt(7));
				eb.setExamFees(rs.getInt(8));
				eb.setDuration(rs.getInt(9));
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return eb;
	}
	
	public ArrayList<ExamBean> getUserEnrolledExams(String userName)
	{
		ArrayList<ExamBean> userExams = new ArrayList<ExamBean>();
		if(conn==null)
		{
			conn = DatabaseConnectionManager.getConnection();
		}
		
		try
		{
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("select a.examId from fees as a, examstatus as b where " +
					"b.result='enrolled' and a.userid=b.userid and a.examId =b.examid and a.userid='"+userName+"'");
			ArrayList<Integer> examIds = new ArrayList<Integer>();
			
			while(rs.next())
			{
				examIds.add(rs.getInt(1));
			}
			
			for(int i=0;i<examIds.size();i++)
			{
				ResultSet rs1 = stmt.executeQuery("select * from exam where examID="+examIds.get(i));
				while(rs1.next())
				{
					ExamBean eb = new ExamBean();
					eb.setExamId(rs1.getInt(1));
					eb.setExamName(rs1.getString(2));
					eb.setSubjectId(rs1.getInt(3));
					eb.setNoOfQuestions(rs1.getInt(4));
					eb.setQuestionIds(rs1.getString(5));
					eb.setMaxMarks(rs1.getInt(6));
					eb.setPassMarks(rs1.getInt(7));
					eb.setExamFees(rs1.getInt(8));
					eb.setDuration(rs1.getInt(9));
					userExams.add(eb);
				}
			}
		}catch(Exception e)
		{
			e.printStackTrace();
		}
		
		return userExams;
	}
	
	public ArrayList<QuestionsBean> getQuestions(int subjectID)
	{
		ArrayList<QuestionsBean> questions = new ArrayList<QuestionsBean>();
		
		if(conn==null)
		{
			conn = DatabaseConnectionManager.getConnection();
		}
		
		try
		{
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("select * from questions where subjectId="+subjectID);
			while(rs.next())
			{
				QuestionsBean qb = new QuestionsBean();
				qb.setQuestionId(rs.getInt(1));
				qb.setSubjectId(rs.getInt(2));
				qb.setQuestiontype(rs.getString(3));
				qb.setQuestion(rs.getString(4));
				
				ArrayList<String> options = new ArrayList<String>();
				String opt = rs.getString(5);
				StringTokenizer st = new StringTokenizer(opt,"|");
				while(st.hasMoreTokens())
				{
					options.add(st.nextToken());
				}
				qb.setOptions(options);
				qb.setCorrectAnswer(rs.getString(6));
				qb.setMarks(rs.getInt(7));
				qb.setCreatedBy(rs.getString(8));
				qb.setCreatedDate(rs.getDate(9));
				qb.setUpdatedBy(rs.getString(10));
				qb.setUpdatedDate(rs.getDate(11));
				questions.add(qb);
			}
		}catch(Exception e)
		{
			e.printStackTrace();
		}
		
		return questions;
		
	}
	
	public ArrayList<QuestionsBean> getExamQuestions(int examID)
	{
		ArrayList<QuestionsBean> questions = new ArrayList<QuestionsBean>();
		
		if(conn==null)
		{
			conn = DatabaseConnectionManager.getConnection();
		}
		
		try
		{
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("select questionIds from exam where examId="+examID);
			if(rs.next())
			{
				String quesIds = rs.getString(1);
				StringTokenizer st = new StringTokenizer(quesIds,"|");
				while(st.hasMoreTokens())
				{
					rs = stmt.executeQuery("select * from questions where questionId="+st.nextToken());
					if(rs.next())
					{
						QuestionsBean qb = new QuestionsBean();
						qb.setQuestionId(rs.getInt(1));
						qb.setSubjectId(rs.getInt(2));
						qb.setQuestiontype(rs.getString(3));
						qb.setQuestion(rs.getString(4));
						
						ArrayList<String> options = new ArrayList<String>();
						String opt = rs.getString(5);
						StringTokenizer stt = new StringTokenizer(opt,"|");
						while(stt.hasMoreTokens())
						{
							options.add(stt.nextToken());
						}
						qb.setOptions(options);
						qb.setCorrectAnswer(rs.getString(6));
						qb.setMarks(rs.getInt(7));
						qb.setCreatedBy(rs.getString(8));
						qb.setCreatedDate(rs.getDate(9));
						qb.setUpdatedBy(rs.getString(10));
						qb.setUpdatedDate(rs.getDate(11));
						questions.add(qb);
					}
				}
			}
			
		}catch(Exception e)
		{
			e.printStackTrace();
		}
		
		return questions;
		
	}
	
	public ArrayList<QuestionsBean> viewExamPaper(int examId)
	{
		ArrayList<QuestionsBean> questions = new ArrayList<QuestionsBean>();
		if(conn==null)
		{
			conn = DatabaseConnectionManager.getConnection();
		}
		
		try
		{
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("select questionIds from exam where examid="+examId);
			if(rs.next())
			{
				String qIds = rs.getString(1);
				StringTokenizer st = new StringTokenizer(qIds,"|");
				while(st.hasMoreTokens())
				{
					QuestionsBean qb = new QuestionsBean();
					rs = stmt.executeQuery("select * from questions where questionId="+st.nextToken());
					if(rs.next())
					{
						qb.setQuestionId(rs.getInt(1));
						qb.setSubjectId(rs.getInt(2));
						qb.setQuestiontype(rs.getString(3));
						qb.setQuestion(rs.getString(4));
						
						ArrayList<String> options = new ArrayList<String>();
						String opt = rs.getString(5);
						StringTokenizer stt = new StringTokenizer(opt,"|");
						while(stt.hasMoreTokens())
						{
							options.add(stt.nextToken());
						}
						qb.setOptions(options);
						qb.setCorrectAnswer(rs.getString(6));
						qb.setMarks(rs.getInt(7));
						qb.setCreatedBy(rs.getString(8));
						qb.setCreatedDate(rs.getDate(9));
						qb.setUpdatedBy(rs.getString(10));
						qb.setUpdatedDate(rs.getDate(11));
					}
					questions.add(qb);
				}
			}else
			{
				
			}
		}
		catch(Exception e)
		{
			
		}
		
		return questions;
	}
	
	
	public Boolean addExam(ExamBean eb, String userName)
	{
		Boolean isAdded = false;
		
		if(conn==null)
		{
			conn = DatabaseConnectionManager.getConnection();
		}
		
		try
		{
			String query = "insert into exam (examName,subjectID,noofQuestions,QuestionIDs,maxMarks,passMarks,examFees,duration,createdBy,creationDate)" +
					"values(?,?,?,?,?,?,?,?,?,?)";
			
			Calendar cal = Calendar.getInstance();
			Date dd = new Date(cal.getTimeInMillis());
			
			PreparedStatement pstmt = conn.prepareStatement(query);
			pstmt.setString(1, eb.getExamName());
			pstmt.setInt(2, eb.getSubjectId());
			pstmt.setInt(3, eb.getNoOfQuestions());
			pstmt.setString(4, eb.getQuestionIds());
			pstmt.setInt(5, eb.getMaxMarks());
			pstmt.setInt(6, eb.getPassMarks());
			pstmt.setInt(7, eb.getExamFees());
			pstmt.setInt(8, eb.getDuration());
			pstmt.setString(9, userName);
			pstmt.setDate(10, dd);
			
			
			int i = pstmt.executeUpdate();
			if(i==1)
			{
				isAdded = true;
			}
			else
			{
				isAdded = false;
			}
			
			
		}catch(Exception e)
		{
			e.printStackTrace();
		}
		
		return isAdded;
	}
	
	public Boolean updateExam(ExamBean eb)
	{
		Boolean isUpdated = false;
		
		if(conn==null)
		{
			conn = DatabaseConnectionManager.getConnection();
		}
		
		try
		{
			String query = "update exam set examName=?,noofQuestions=?," +
					"QuestionIDs=?,maxMarks=?,passMarks=?,examFees=?,duration=? where examID=?";
			
			PreparedStatement pstmt = conn.prepareStatement(query);
			pstmt.setString(1, eb.getExamName());
			pstmt.setInt(2, eb.getNoOfQuestions());
			pstmt.setString(3, eb.getQuestionIds());
			pstmt.setInt(4, eb.getMaxMarks());
			pstmt.setInt(5, eb.getPassMarks());
			pstmt.setInt(6, eb.getExamFees());
			pstmt.setInt(7, eb.getDuration());
			pstmt.setInt(8, eb.getExamId());
			
			
			int i = pstmt.executeUpdate();
			if(i==1)
			{
				isUpdated = true;
			}
			else
			{
				isUpdated = false;
			}
			
			
		}catch(Exception e)
		{
			e.printStackTrace();
		}
		
		return isUpdated;
	}
	
	public Boolean deleteExam(int examID)
	{
		Boolean isDeleted = false;
		
		if(conn==null)
		{
			conn = DatabaseConnectionManager.getConnection();
		}
		
		try
		{
			String query = "delete from exam where examid=?";
			PreparedStatement pstmt = conn.prepareStatement(query);
			pstmt.setInt(1,examID);
			
			int i = pstmt.executeUpdate();
			if(i==1)
			{
				isDeleted = true;
			}
			else
			{
				isDeleted = false;
			}
			
			
		}catch(Exception e)
		{
			e.printStackTrace();
		}
		
		return isDeleted;
	}
	
	//--------------------------------- end of exam related operations -----------------------------------
	//--------------------------------- question related operations -----------------------------------
	
	
	public Boolean addQuestion(QuestionsBean qb)
	{
		Boolean isAdded = false;
		
		if(conn==null)
		{
			conn = DatabaseConnectionManager.getConnection();
		}
		
		try
		{
			String query = "insert into questions (subjectID,QuestionType,question,options,correctAnswer,createdBy,creationDate,updatedBy,updatedDate,marks)" +
					"values(?,?,?,?,?,?,?,?,?,?)";
			
			Calendar cal = Calendar.getInstance();
			Date dd = new Date(cal.getTimeInMillis());
			String ss = "";
			for(int i=0;i<qb.getOptions().size();i++)
			{
				if(i==qb.getOptions().size()-1)
				{
					ss = ss+ qb.getOptions().get(i);
				}else
				{
					ss = ss+ qb.getOptions().get(i)+"|";
				}
			}
			PreparedStatement pstmt = conn.prepareStatement(query);
			pstmt.setInt(1, qb.getSubjectId());
			pstmt.setString(2, qb.getQuestiontype());
			pstmt.setString(3, qb.getQuestion());
			pstmt.setString(4, ss);
			pstmt.setString(5, qb.getCorrectAnswer());
			pstmt.setString(6, qb.getCreatedBy());
			pstmt.setDate(7, dd);
			pstmt.setString(8, qb.getUpdatedBy());
			pstmt.setDate(9, dd);
			pstmt.setInt(10, qb.getMarks());
			
			int i = pstmt.executeUpdate();
			if(i==1)
			{
				isAdded = true;
			}
			else
			{
				isAdded = false;
			}
			
			
		}catch(Exception e)
		{
			e.printStackTrace();
		}
		
		
		return isAdded;
	}
	
	public Boolean updateQuestion(QuestionsBean qb)
	{
		Boolean isUpdated = false;
		
		if(conn==null)
		{
			conn = DatabaseConnectionManager.getConnection();
		}
		
		try
		{
			String query = "update questions set question=?,options=?," +
					"correctAnswer=?,marks=?,updatedBy=?,updatedDate=? where questionid="+qb.getQuestionId();
			
			Calendar cal = Calendar.getInstance();
			Date dd = new Date(cal.getTimeInMillis());
			String ss = "";
			for(int i=0;i<qb.getOptions().size();i++)
			{
				if(i==qb.getOptions().size()-1)
				{
					ss = ss+ qb.getOptions().get(i);
				}else
				{
					ss = ss+ qb.getOptions().get(i)+"|";
				}
			}
			PreparedStatement pstmt = conn.prepareStatement(query);
			pstmt.setString(1, qb.getQuestion());
			pstmt.setString(2, ss);
			pstmt.setString(3, qb.getCorrectAnswer());
			pstmt.setInt(4, qb.getMarks());
			pstmt.setString(5, qb.getUpdatedBy());
			pstmt.setDate(6, dd);
			
			
			int i = pstmt.executeUpdate();
			if(i==1)
			{
				isUpdated = true;
			}
			else
			{
				isUpdated = false;
			}
			
			
		}catch(Exception e)
		{
			e.printStackTrace();
		}
		
		return isUpdated;
	}
	
	public Boolean deleteQuestion(int questionId)
	{
		Boolean isDeleted = false;
		
		if(conn==null)
		{
			conn = DatabaseConnectionManager.getConnection();
		}
		
		try
		{
			String query = "delete from questions where questionId=?";
			PreparedStatement pstmt = conn.prepareStatement(query);
			pstmt.setInt(1,questionId);
			
			int i = pstmt.executeUpdate();
			if(i==1)
			{
				isDeleted = true;
			}
			else
			{
				isDeleted = false;
			}
			
			
		}catch(Exception e)
		{
			e.printStackTrace();
		}
		
		return isDeleted;
	}
	
	
	
	//--------------------------------- end of question related operations -----------------------------------
	
	public Boolean doPayment(PaymentBean pb)
	{
		Boolean paymentSuccess = false;
		
		if(conn==null)
		{
			conn = DatabaseConnectionManager.getConnection();
		}
		
		try
		{
			String query = "insert into fees (FeesAmount,UserID,subjectID,examID)" +
					"values(?,?,?,?)";
			PreparedStatement pstmt = conn.prepareStatement(query);
			pstmt.setInt(1,pb.getExamFees());
			pstmt.setString(2,pb.getUserName());
			pstmt.setInt(3,pb.getSubjectId());
			pstmt.setInt(4,pb.getExamId());
			
			int i = pstmt.executeUpdate();
			if(i==1)
			{
				paymentSuccess = true;
			}
			else
			{
				paymentSuccess = false;
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		
		return paymentSuccess;
	}
	
	public Boolean chechIfPaymentDone(int examId, String userId)
	{
		Boolean isPaymentDone = false;
		if(conn==null)
		{
			conn = DatabaseConnectionManager.getConnection();
		}
		
		try
		{
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("select * from fees where examID="+examId+" AND userID='"+userId+"'");
			if(rs.next())
			{
				isPaymentDone = true;
			}
			else
			{
				isPaymentDone = false;
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return isPaymentDone;
	}
	
	public Boolean checkUserExam(int examId, String userId)
	{
		Boolean isCompleted = false;
		
		if(conn==null)
		{
			conn = DatabaseConnectionManager.getConnection();
		}
		
		try
		{
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("select * from examstatus where examID="+examId+" AND userID='"+userId+"' AND result!='enrolled'");
			if(rs.next())
			{
				isCompleted = true;
			}
			else
			{
				isCompleted = false;
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		
		return isCompleted;
	}
	
	//==============================
	
	public Boolean addExamStatus(ExamStatusBean esb)
	{
		Boolean isAdded = false;
		if(conn==null)
		{
			conn = DatabaseConnectionManager.getConnection();
		}
		
		try
		{
			Calendar cal = Calendar.getInstance();
			Date dd = new Date(cal.getTimeInMillis());
			
			String query = "insert into examstatus (examID,subjectId, userID,score,result,examCompletionDate)" +
					"values(?,?,?,?,?,?)";
			PreparedStatement pstmt = conn.prepareStatement(query);
			pstmt.setInt(1,esb.getExamId());
			pstmt.setInt(2,esb.getSubjectId());
			pstmt.setString(3,esb.getUserName());
			pstmt.setInt(4,esb.getScore());
			pstmt.setString(5,esb.getResult());
			pstmt.setDate(6,dd);
			
			int i = pstmt.executeUpdate();
			if(i==1)
			{
				isAdded = true;
			}
			else
			{
				isAdded = false;
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return isAdded;
	}
	
	public ExamStatusBean updateExamStatus(ExamStatusBean esb)
	{
		ExamStatusBean esb1 = new ExamStatusBean();
		
		if(conn==null)
		{
			conn = DatabaseConnectionManager.getConnection();
		}
		
		try
		{
			Calendar cal = Calendar.getInstance();
			Date dd = new Date(cal.getTimeInMillis());
			
			String query = "update examstatus set score=?,result=?,examCompletionDate=? where userID=? and examID=?";
			PreparedStatement pstmt = conn.prepareStatement(query);
			pstmt.setInt(1,esb.getScore());
			pstmt.setString(2,esb.getResult());
			pstmt.setDate(3,dd);
			pstmt.setString(4,esb.getUserName());
			pstmt.setInt(5,esb.getExamId());
			
			
			int i = pstmt.executeUpdate();
			if(i==1)
			{
				esb1 = esb;
			}
			else
			{
				esb1=null;
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return esb1;
	}
	
}
